package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * DBConnection is manager class that deals with creating and maintaining connection
 * to an Oracle DB along with executing SQL statements and queries *
 */
public class DBConnection {
	
	// instance of DBConnection for singleton implementation
	private static DBConnection dbCon = null;
	
	// constant fields for connecting to the DB	
	private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:ug"; 
	private static final String DB_ACCT = "ora_w6c6";
	private static final String DB_PASS = "a65122061";
	
	// connection to the DB
	private Connection sqlCon = null;
	
	/**
	 * Constructor for DBConnection class
	 * Responsible for registering the Oracle Driver
	 */
	private DBConnection()
	{
		try
		{
			// register Oracle driver
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		}
		catch (SQLException e)
		{
			System.out.println("Message: " + e.getMessage());
		}		
	}
	
	/**
	 * GetInstance returns an instance of the DBConnection class if it has already
	 * been initialized
	 * 
	 * @return singleton instance of DBConnection
	 */
	public static DBConnection getInstance()
	{
		// check that DBConnection has already been initialized
		if(dbCon == null)
		{
			dbCon = new DBConnection();
		}
		
		return dbCon;
	}
	
	/**
	 * Connect to the oracle DB
	 * 
	 * @return if the connection was successful
	 */
	public boolean connect()
	{		
		try
		{	
			// get db connection
			sqlCon = DriverManager.getConnection(DB_URL, DB_ACCT, DB_PASS);
			System.out.println("Connected to Oracle DB: " + DB_URL);
			
			// disable auto commits;
			sqlCon.setAutoCommit(false);	
			
			return true;			
		} 
		catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			return false;
		}	
	}
	
	/**
	 * Disconnect from the oracle DB
	 * 
	 * @return if the disconnect was successful for not
	 */
	public boolean disconnect()
	{
		try {
			// attempt to disconnect
			sqlCon.close();
			
			// set connection related fields to null
			sqlCon = null;
			
			return true;
		} 
		catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			return false;
		}	
	}
	
	/**
	 * ExecuteStatement executes the provided string statement
	 * 
	 * @param sqlStatement the statement to be executed
	 * @return
	 */
	public void executeStatement(String sqlStatement) throws SQLException
	{
		// execute the statement
		Statement stmt = sqlCon.createStatement();
		stmt.executeUpdate(sqlStatement);	
		stmt.close();
	}
	
	/**
	 * ExecuteQuery executes the provided string query
	 * 
	 * @param sqlQuery the query to be executed
	 * @return
	 */
	public ResultSet executeQuery(String sqlQuery) throws SQLException
	{
		ResultSet rs = null;
		
		// execute the statement
		Statement stmt = sqlCon.createStatement();
		if(stmt != null)
		{
			rs = stmt.executeQuery(sqlQuery);
		}
		
		return rs;		
	}
	
	/**
	 * Commit exposes the SQL connection's commit method to users of the DBConnection class
	 * 
	 * @throws SQLException
	 */
	public void commit() throws SQLException 
	{
		sqlCon.commit();
	}
	
	/**
	 * Rollback exposes the SQL connection's rollback method to users of the DBConnection class
	 * 
	 * @throws SQLException
	 */
	public void rollback() throws SQLException
	{
		sqlCon.rollback();
	}
}
